package zhtt;

        import org.hibernate.Session;
        import org.hibernate.criterion.Order;
        import org.hibernate.criterion.Restrictions;
        import org.junit.Before;
        import org.junit.Test;

        import java.util.List;

/**
 * Created by zhtt on 2017/4/10.
 */
public class StudentJunitTest_order {

    private Session session;

    @Before
    public void setUp(){
        session=HbnUtil.getSession();
    }

    @org.junit.Test
    /** SQL查询  使用“？”占位符占位， setXxx()绑定参数**/
    public void sql(){
        try{
            session.beginTransaction();
            String sql="select * from zhtt0_student where age>? and score>? ";//用表名
            List<Student> studentList=session.createSQLQuery(sql)
                    .addEntity(Student.class)
                    .setInteger(0,20)
                    .setDouble(1,82)
                    .list();
            for(Student student:studentList){
                System.out.println(student);
            }
            session.getTransaction().commit();
        }catch (Exception e){
            e.printStackTrace();
        }
    }

    @Test/** SQL查询  使用“？”占位符占位， setParamter()绑定参数**/
    public void sql2(){
        try{
            session.beginTransaction();
            String sql="select * from zhtt0_student where age>? and score>? ";//用表名
            List<Student> studentList=session.createSQLQuery(sql)
                    .addEntity(Student.class)
                    .setParameter(0,20)
                    .setParameter(1,84)
                    .list();
            for(Student student:studentList){
                System.out.println(student);
            }
            session.getTransaction().commit();
        }catch (Exception e){
            e.printStackTrace();
        }
    }


    @Test/** SQL查询  别名 **/
    public void sql3(){
        try{
            session.beginTransaction();
            String sql="select * from zhtt0_student where age>:age and score>:score ";//用表名
            List<Student> studentList=session.createSQLQuery(sql)
                    .addEntity(Student.class)
                    .setParameter("age",20)
                    .setParameter("score",84)
                    .list();
            for(Student student:studentList){
                System.out.println(student);
            }
            session.getTransaction().commit();
        }catch (Exception e){
            e.printStackTrace();
        }
    }

    @Test /** HQL查询  Hibernate query language  (以上三种都支持)**/
    public void hql(){
        try{
            session.beginTransaction();
            String hql="from Student where id>? and age>? and score>:score ";///属性名
            List<Student> studentList=session.createQuery(hql)
                    .setInteger(0,2)
                    .setParameter(1,20)
                    .setParameter("score",20.5)// 此值必须有小数，不然查询不到
                    .list();
            for(Student student:studentList){
                System.out.println(student);
            }
            session.getTransaction().commit();
        }catch (Exception e){}
    }

    @Test
    /** Query By Criteria **/
    public void qbc(){
        try{
            session.beginTransaction();
            List<Student> studentList=session.createCriteria(Student.class)
                    .add(Restrictions.gt("age",20))//属性名
                    .add(Restrictions.gt("score",80.4))//属性名 此值必须有小数，不然查询不到
                    .list();//属性名
            for(Student student:studentList){
                System.out.println(student);
            }
            session.getTransaction().commit();
        }catch (Exception e){}
    }

}
